﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SchoolManagementSystem
{
    public partial class Keuangan : Form
    {
        private KeuanganAdd keuanganadd = null;
        private KeuanganEdit keuanganedit = null;
        private List<int> cat_id = new List<int>();
        private DataTable dTable = null;
        private BindingSource bindingSource = new BindingSource();
        private int selectedMonth = -1;
        private int selectedYear = -1;
        private int selectedType = -1;
        private int selectedCategory = -1;

        public Keuangan()
        {
            InitializeComponent();
            Style.setDatagridviewStyle(dgvKeuangan);
        }

        private enum Category { PEMASUKAN, PENGELUARAN }
        private void loadKategori(Category category)
        {
            string sql;
            if (category == Category.PENGELUARAN)
                sql = "SELECT * FROM Expense_type";
            else
                sql = "SELECT * FROM Income_type";

            cat_id.Clear();
            cat_id.Add(0);

            cmbKategori.Items.Clear();
            cmbKategori.Items.Add("-Semua-");

            DataTable dt = Database.getInstance().query(sql);
            foreach (DataRow row in dt.Rows)
            {
                cat_id.Add((int)row[0]);
                cmbKategori.Items.Add(row[1].ToString());
            }
        }

        private void btnTambah_Click(object sender, EventArgs e)
        {
            if (this.keuanganadd == null || this.keuanganadd.IsDisposed)
                keuanganadd = new KeuanganAdd();

            //check whether the form has been opened or not
            if (((Main)this.MdiParent).isOpenForm(keuanganadd))
            {
                keuanganadd = null;
                return;
            }

            keuanganadd.MdiParent = this.MdiParent;
            keuanganadd.Show();
            keuanganadd.Activate();
        }

        private void cmbTipe_SelectedIndexChanged(object sender, EventArgs e)
        {
            int select = cmbTipe.SelectedIndex;
            cmbKategori.Enabled = true;
            if (select == 0)
            {
                cmbKategori.SelectedIndex = 0;
                cmbKategori.Enabled = false;
            }
            else if (select == 1)
            {
                // Load kategori pengeluaran
                loadKategori(Category.PENGELUARAN);
            }
            else if (select == 2)
            {
                // Load kategori pemasukan
                loadKategori(Category.PEMASUKAN);
            }

            this.selectedType = select;

            updateDgvKeuangan();
        }

        private void Keuangan_Load(object sender, EventArgs e)
        {
            int year = DateTime.Now.Year;

            this.selectedMonth = DateTime.Now.Month;
            this.cmbBulan.SelectedIndex = this.selectedMonth;

            this.selectedYear = DateTime.Now.Year;
            int indexYear = 1;
            for (int i = year; i > year - 7; i--)
            {
                cmbTahun.Items.Add(i.ToString());
                if (i.ToString() == this.selectedYear.ToString())
                {
                    cmbTahun.SelectedIndex = indexYear;
                }
                indexYear++;
            }

            updateDgvKeuangan();       
        }

        public void refreshTableData()
        {
            this.dgvKeuangan.Columns.Clear();

            dTable = Database.getInstance().query("select f.*,a.acc_name from Finance AS f INNER JOIN Account AS a ON f.acc_id=a.acc_id");

            dTable.Columns.Add("Kategori");
            dTable.Columns.Add("Debit");
            dTable.Columns.Add("Kredit");

            for (int i = 0; i < dTable.Rows.Count; i++)
            {
                string sql = null;

                if (dTable.Rows[i]["type"].ToString() == "PENGELUARAN")
                {
                    sql = "SELECT * FROM Expense_type";     
                    DataTable category = Database.getInstance().query(sql);
                    foreach (DataRow rowCategory in category.Rows)
                    {
                        if (dTable.Rows[i]["expense_id"].ToString() == rowCategory["id"].ToString())
                        {
                            dTable.Rows[i]["Kategori"] = rowCategory["type"];
                            break;
                        }
                    }

                    dTable.Rows[i]["Kredit"] = dTable.Rows[i]["amount"];
                }
                else if (dTable.Rows[i]["type"].ToString() == "PEMASUKAN")
                {
                    sql = "SELECT * FROM Income_type";
                    DataTable category = Database.getInstance().query(sql);
                    foreach (DataRow rowCategory in category.Rows)
                    {
                        if (dTable.Rows[i]["income_id"].ToString() == rowCategory["id"].ToString())
                        {
                            dTable.Rows[i]["Kategori"] = rowCategory["type"];
                            break;
                        }
                    }

                    dTable.Rows[i]["Debit"] = dTable.Rows[i]["amount"];
                }
            }
          
            bindingSource.DataSource = dTable;
            this.dgvKeuangan.DataSource = bindingSource;
         
            //// Add Edit Column
            //DataGridViewLinkColumn editColumn = new DataGridViewLinkColumn();
            //editColumn.UseColumnTextForLinkValue = true;
            //editColumn.Name = "Edit";
            //editColumn.Text = "Edit";
            //editColumn.HeaderText = "Edit";
            //editColumn.ReadOnly = true;
            //editColumn.VisitedLinkColor = Color.Blue;
            //this.dgvKeuangan.Columns.Add(editColumn);
            //this.dgvKeuangan.Columns["Edit"].Width = 50;
            //this.dgvKeuangan.Columns["Edit"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            //// Add Delete Column
            //DataGridViewLinkColumn deleteColumn = new DataGridViewLinkColumn();
            //deleteColumn.UseColumnTextForLinkValue = true;
            //deleteColumn.Name = "Delete";
            //deleteColumn.HeaderText = "Delete";
            //deleteColumn.Text = "Delete";
            //deleteColumn.ReadOnly = true;
            //deleteColumn.VisitedLinkColor = Color.Blue;
            //this.dgvKeuangan.Columns.Add(deleteColumn);
            //this.dgvKeuangan.Columns["Delete"].Width = 50;
            //this.dgvKeuangan.Columns["Delete"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            this.dgvKeuangan.Columns["id"].HeaderText = "ID";
            this.dgvKeuangan.Columns["time"].HeaderText = "Waktu Input Data";
            this.dgvKeuangan.Columns["receipt_no"].HeaderText = "No Kwitansi";
            this.dgvKeuangan.Columns["description"].HeaderText = "Keterangan";

            this.dgvKeuangan.Columns["type"].Visible = false;
            this.dgvKeuangan.Columns["income_id"].Visible = true;
            this.dgvKeuangan.Columns["expense_id"].Visible = true;
            this.dgvKeuangan.Columns["spp_id"].Visible = false;
            this.dgvKeuangan.Columns["acc_id"].Visible = false;
            this.dgvKeuangan.Columns["amount"].Visible = false;
      
            updateAmount(dTable.Rows);
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            this.cmbBulan.SelectedIndex = 0;
            this.cmbTahun.SelectedIndex = 0;

            this.refreshTableData();
        }

        private void dgvKeuangan_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (this.dgvKeuangan.Columns[e.ColumnIndex].Name == "Edit")
            {

                if ((this.dgvKeuangan.Rows[e.RowIndex].Cells["income_id"].Value.ToString() == "1") || (this.dgvKeuangan.Rows[e.RowIndex].Cells["expense_id"].Value.ToString() == "1"))
                {
                    MessageBox.Show("Maaf, Anda tidak bisa edit data ini !", "Peringatan !", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }

                editData(e.RowIndex);
            }
            else if (this.dgvKeuangan.Columns[e.ColumnIndex].Name == "Delete")
            {
                deleteData(e.RowIndex);
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (dgvKeuangan.SelectedRows.Count < 1)
            {
                MessageBox.Show("Mohon pilih salah satu item", "Perhatian!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            DataGridViewRow row = dgvKeuangan.CurrentRow;
            if (row.Cells["income_id"].Value.ToString() == "1" || row.Cells["expense_id"].Value.ToString() == "1")
            {
                MessageBox.Show("Data dengan kategori ini tidak dapat diubah", "Peringatan!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            editData(this.dgvKeuangan.CurrentCell.RowIndex);  
        }

        private void btnHapus_Click(object sender, EventArgs e)
        {
            if (dgvKeuangan.SelectedRows.Count < 1)
            {
                MessageBox.Show("Mohon pilih salah satu item", "Perhatian!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            deleteData(this.dgvKeuangan.CurrentCell.RowIndex);
        }

        private void deleteData(int rowIndex)
        {
            var result = MessageBox.Show("Apakah Anda ingin menghapus data tersebut ?", "Peringatan !", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
            if (result == System.Windows.Forms.DialogResult.OK)
            {
                DataTable dt = Database.getInstance().query("SELECT * FROM Finance");
                dt.Rows[rowIndex].Delete();
                Database.getInstance().updateDB(dt);

                this.dgvKeuangan.Rows.RemoveAt(rowIndex);
                this.dgvKeuangan.Refresh();
            }
        }

        private void editData(int rowIndex)
        {
            if (this.keuanganedit == null || this.keuanganedit.IsDisposed)
                keuanganedit = new KeuanganEdit(this, this.dTable, rowIndex,
                    this.dgvKeuangan.Rows[rowIndex].Cells["receipt_no"].Value.ToString(),
                    this.dgvKeuangan.Rows[rowIndex].Cells["type"].Value.ToString(),
                    this.dgvKeuangan.Rows[rowIndex].Cells["expense_id"].Value,
                    this.dgvKeuangan.Rows[rowIndex].Cells["income_id"].Value,
                    this.dgvKeuangan.Rows[rowIndex].Cells["description"].Value.ToString(),
                    this.dgvKeuangan.Rows[rowIndex].Cells["amount"].Value);

            this.keuanganedit.MdiParent = this.MdiParent;
            this.keuanganedit.Show();
            this.keuanganedit.Activate();
        }

        private void cmbBulan_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.selectedMonth = cmbBulan.SelectedIndex;

            updateDgvKeuangan();

            /* 
            string sql = null;

            this.dgvKeuangan.Columns.Clear();

            if (cmbBulan.SelectedIndex == 0)
            {
                sql = "SELECT * FROM Finance";
            }
            else
            {
                sql = "SELECT * FROM Finance WHERE MONTH([time]) = " + this.cmbBulan.SelectedIndex;
            }

            dTable = Database.getInstance().query(sql);

            bindingSource.DataSource = dTable;
            this.dgvKeuangan.DataSource = bindingSource;

            updateAmount(dTable.Rows);
             * */ 

            // string sql = "SELECT FROM Finance WHERE YEAR([date]) = " + year + " AND MONTH([date]) = " + month;
        }

        private void updateAmount(DataRowCollection rows)
        {
            decimal sum = 0;
            decimal amountDebit = 0;
            decimal amountKredit = 0;

            for (int i = 0; i < rows.Count; i++)
            {
                if (rows[i]["Debit"].ToString().Length == 0)
                {
                    // Do nothing
                }
                else
                {
                    decimal debit = Decimal.Parse(rows[i]["Debit"].ToString());
                    amountDebit += debit;
                }

                if (rows[i]["Kredit"].ToString().Length == 0)
                {
                    // Do nothing
                }
                else
                {
                    decimal kredit = Decimal.Parse(rows[i]["Kredit"].ToString());
                    amountKredit += kredit;
                }
            }
            sum = amountDebit - amountKredit;
            this.txtTotal.Text = sum.ToString();
        }

        /* 
        private void updateDgvKeuangan()
        {
            string sql = null;

            if ((this.selectedMonth == -1) || (this.selectedYear == -1) || (this.selectedType == -1))
            {
                // Set default command
                this.selectedMonth = 0;
                this.selectedYear = 0;
                this.selectedType = 0;
            }

            /// sql = "SELECT * FROM Finance WHERE MONTH([time]) = " + this.cmbBulan.SelectedIndex + "AND YEAR([time]) = " + this.selectedYear;

            if ((this.selectedMonth == 0) && (this.selectedYear == 0) && (this.selectedType == 0) && (this.selectedCategory == 0))
            {
                sql = "SELECT * FROM Finance";
            }
            else
            {
                sql = "SELECT * FROM Finance WHERE MONTH([time]) = " + this.cmbBulan.SelectedIndex + "AND YEAR([time]) = " + this.selectedYear + "";
            }

            
            this.dgvKeuangan.Columns.Clear();

            dTable = Database.getInstance().query(sql);

            bindingSource.DataSource = dTable;
            this.dgvKeuangan.DataSource = bindingSource;

            updateAmount(dTable.Rows);
        }
         * */ 

        private void cmbTahun_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbTahun.SelectedIndex == 0)
            {
                this.selectedYear = 0;
            }
            else
            {
                this.selectedYear = int.Parse(cmbTahun.SelectedItem.ToString());
            }
           
            updateDgvKeuangan();
        }

        /* 
        private void updateDgvKeuanganByCategory()
        {
            string sql = null;

            if ((this.selectedMonth == -1) || (this.selectedYear == -1) || (this.selectedType == -1) || (this.selectedCategory == -1))
            {
                this.selectedMonth = 0;
                this.selectedYear = 0;
                this.selectedType = 0;
                this.selectedCategory = 0;
            }

            // Default command
            if ((this.selectedMonth == 0) && (this.selectedYear == 0) && (this.selectedType == 0) && (this.selectedCategory == 0))
            {
                sql = "SELECT * FROM Finance";
                return;
            }
        }
         * */ 

        // Check month 
        private Boolean isMonthSelected()
        {
            if (this.selectedMonth > 0)
                return true;
            
            return false;
        }

        // Check year
        private Boolean isYearSelected()
        {
            if (this.selectedYear > 0)
                return true;

            return false;
        }

        // Check type
        private Boolean isTypeSelected()
        {
            if (this.selectedType >= 0)
                return true;

            return false;
        }

        // Check category
        private Boolean isCategorySelected()
        {
            if (this.selectedCategory >= 0)
                return true;

            return false;
        }

        private void updateDgvKeuangan()
        {
            if (this.selectedMonth == -1) this.selectedMonth = 0;
            if (this.selectedYear == -1) this.selectedYear = 0;
            if (this.selectedType == -1) this.selectedType = 0;
            if (this.selectedCategory == -1) this.selectedCategory = 0;
            
            updateDgvKeuanganByCategory(isMonthSelected(), isYearSelected(), isTypeSelected(), isCategorySelected());
        }

        private void updateDgvKeuanganByCategory(Boolean month, Boolean year, Boolean type, Boolean category)
        {
            string sql = null;

            if (month)
            {
                sql = "SELECT * FROM Finance WHERE MONTH([time]) = " + this.selectedMonth;
                if (year)
                {
                    string add_year = " AND YEAR([time]) = " + this.selectedYear;
                    sql += add_year;

                    if (type)
                    {
                        switch (this.selectedType)
                        {
                            case 0:
                                // Select all type
                                break;

                            case 1:
                                // Select expense only
                                if (this.selectedCategory == 0)
                                {
                                    string add_type = " AND type='PENGELUARAN'";
                                    sql += add_type;
                                }
                                else
                                {
                                    string add_type = " AND type='PENGELUARAN' AND expense_id='" + this.selectedCategory + "'";
                                    sql += add_type;
                                }
                                break;

                            case 2:
                                // Select income only
                                if (this.selectedCategory == 0)
                                {
                                    string add_type = " AND type='PEMASUKAN'";
                                    sql += add_type;
                                }
                                else
                                {
                                    string add_type = " AND type='PEMASUKAN' AND income_id='" + this.selectedCategory + "'";
                                    sql += add_type;
                                }
                                break;

                            default:
                                break;
                        }
                    }
                }
            }
            else
            {
                if (year)
                {
                    sql = "SELECT * FROM Finance WHERE YEAR([time]) = " + this.selectedYear;
                    if (type)
                    {
                        switch (this.selectedType)
                        {
                            case 0:
                                // Select all type
                                break;

                            case 1:
                                // Select expense only
                                if (this.selectedCategory == 0)
                                {
                                    string add_type = " AND type='PENGELUARAN'";
                                    sql += add_type;
                                }
                                else
                                {
                                    string add_type = " AND type='PENGELUARAN' AND expense_id='" + this.selectedCategory + "'";
                                    sql += add_type;
                                }
                                break;

                            case 2:
                                // Select income only
                                if (this.selectedCategory == 0)
                                {
                                    string add_type = " AND type='PEMASUKAN'";
                                    sql += add_type;
                                }
                                else
                                {
                                    string add_type = " AND type='PEMASUKAN' AND income_id='" + this.selectedCategory + "'";
                                    sql += add_type;
                                }
                                break;

                            default:
                                break;
                        }
                    }
                }
                else
                {
                    if (type)
                    {
                        switch (this.selectedType)
                        {
                            case 0:
                                sql = "SELECT * FROM Finance";
                                break;

                            case 1:
                                if (this.selectedCategory == 0)
                                {
                                    // Select all expense_id category
                                    sql = "SELECT * FROM Finance WHERE type='PENGELUARAN'";
                                }
                                else
                                {
                                    sql = "SELECT * FROM Finance WHERE type='PENGELUARAN' AND expense_id='" + this.selectedCategory + "'";
                                }
                                break;

                            case 2:
                                // Select income only
                                if (this.selectedCategory == 0)
                                {
                                    string add_type = "SELECT * FROM Finance WHERE type='PEMASUKAN'";
                                    sql += add_type;
                                }
                                else
                                {
                                    string add_type = "SELECT * FROM Finance WHERE type='PEMASUKAN' AND income_id='" + this.selectedCategory + "'";
                                    sql += add_type;
                                }
                                break;

                            default:
                                break;
                        }
                    }
                }
            }

            this.dgvKeuangan.Columns.Clear();

            dTable = Database.getInstance().query(sql);

            dTable.Columns.Add("Kategori");
            dTable.Columns.Add("Debit");
            dTable.Columns.Add("Kredit");

            for (int i = 0; i < dTable.Rows.Count; i++)
            {
                string command = null;

                if (dTable.Rows[i]["type"].ToString() == "PENGELUARAN")
                {
                    command = "SELECT * FROM Expense_type";
                    DataTable categoryDt = Database.getInstance().query(command);
                    foreach (DataRow rowCategory in categoryDt.Rows)
                    {
                        if (dTable.Rows[i]["expense_id"].ToString() == rowCategory["id"].ToString())
                        {
                            dTable.Rows[i]["Kategori"] = rowCategory["type"];
                            break;
                        }
                    }

                    dTable.Rows[i]["Kredit"] = dTable.Rows[i]["amount"];
                }
                else if (dTable.Rows[i]["type"].ToString() == "PEMASUKAN")
                {
                    command = "SELECT * FROM Income_type";
                    DataTable categoryDt = Database.getInstance().query(command);
                    foreach (DataRow rowCategory in categoryDt.Rows)
                    {
                        if (dTable.Rows[i]["income_id"].ToString() == rowCategory["id"].ToString())
                        {
                            dTable.Rows[i]["Kategori"] = rowCategory["type"];
                            break;
                        }
                    }

                    dTable.Rows[i]["Debit"] = dTable.Rows[i]["amount"];
                }
            }

            bindingSource.DataSource = dTable;
            this.dgvKeuangan.DataSource = bindingSource;

            //// Add Edit Column
            //DataGridViewLinkColumn editColumn = new DataGridViewLinkColumn();
            //editColumn.UseColumnTextForLinkValue = true;
            //editColumn.Name = "Edit";
            //editColumn.Text = "Edit";
            //editColumn.HeaderText = "Edit";
            //editColumn.ReadOnly = true;
            //editColumn.VisitedLinkColor = Color.Blue;
            //this.dgvKeuangan.Columns.Add(editColumn);
            //this.dgvKeuangan.Columns["Edit"].Width = 50;
            //this.dgvKeuangan.Columns["Edit"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            //// Add Delete Column
            //DataGridViewLinkColumn deleteColumn = new DataGridViewLinkColumn();
            //deleteColumn.UseColumnTextForLinkValue = true;
            //deleteColumn.Name = "Delete";
            //deleteColumn.HeaderText = "Delete";
            //deleteColumn.Text = "Delete";
            //deleteColumn.ReadOnly = true;
            //deleteColumn.VisitedLinkColor = Color.Blue;
            //this.dgvKeuangan.Columns.Add(deleteColumn);
            //this.dgvKeuangan.Columns["Delete"].Width = 50;
            //this.dgvKeuangan.Columns["Delete"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            this.dgvKeuangan.Columns["id"].HeaderText = "ID";
            this.dgvKeuangan.Columns["time"].HeaderText = "Waktu Input Data";
            this.dgvKeuangan.Columns["receipt_no"].HeaderText = "No Kwitansi";
            this.dgvKeuangan.Columns["description"].HeaderText = "Keterangan";

            this.dgvKeuangan.Columns["type"].Visible = false;
            this.dgvKeuangan.Columns["income_id"].Visible = false;
            this.dgvKeuangan.Columns["expense_id"].Visible = false;
            this.dgvKeuangan.Columns["spp_id"].Visible = false;
            this.dgvKeuangan.Columns["acc_id"].Visible = false;
            this.dgvKeuangan.Columns["amount"].Visible = false;

            updateAmount(dTable.Rows);
        }

        private void cmbKategori_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.selectedCategory = cmbKategori.SelectedIndex;

            updateDgvKeuangan();
        }
    }
}
